**************************************************
* This code file set up the data and produces the
* figures and tables for the article:
*
* "THE QUALITY AND EFFICIENCY OF PUBLIC AND 
* PRIVATE FIRMS: EVIDENCE FROM AMBULANCE SERVICES"
*
* Accepted for publication at the
* Quarterly Journal of Economics
*
* By Daniel Knutsson and Björn Tyrefors
* Research Institute of Industrial Economics
* Started 2021, final version 2022-02-10.
**************************************************

**************************************************
* This code file produce the following tables and figures:
**************************
* TABLE VI in article
* TABLE B.6 in OL Appendix
* TABLE B.10 in OL Appendix
* FIGURE A.4 in OL Appendix
**************************************************

**************************************************
* PART 1:
* In this first part of this code file,
* the following data sources from Statistics Sweden was
* used to produce the output:
*
* LISA 1990-2017
* RTB 2009-2017
* IoT 2009-2016
* UREG (utbildningsregistret) (education) 2009-2016
* Conscription data (matched to population)

* Statistics Sweden has already merged several of
* these sources beforehand. We only add conscription data
* and create the variables we use in the following code.
***************************************************


*****************************************
* Start data load
*****************************************

* Load data to take out population working in ambulance firms
* in Stockholm in 2009-2017 with income and education.

forval x=2009/2017 {

* Load ODBC file to STATA
odbc load, exec("SELECT * FROM Individ_`x';") dsn("P1016")

* Different variables available at different years
if `x'>2013 {
keep P1016_LopNr_PersonNr P1016_LopNr_KU1PeOrgNr FodelseAr LoneInk Kon Alder Sun2000Inr Sun2000niva ExamAr AntAns SektorKod KU1Ink KU1SektorKod KU1AstLan KU1AstSNI2007 KU1Ssyk4 KU1Ssyk4_2012
}
else {
keep P1016_LopNr_PersonNr P1016_LopNr_KU1PeOrgNr FodelseAr LoneInk Kon Alder Sun2000Inr Sun2000niva ExamAr AntAns SektorKod KU1Ink KU1SektorKod KU1AstLan KU1AstSNI2007 KU1Ssyk4
}

* Keep if either sector code is ambulance firm (SNI) or nurse is specialist (Sun2000inr)
* and working in Stockholm Region (AstLan).
keep if (KU1AstSNI2007=="86902" | Sun2000Inr=="723c") & KU1AstLan=="01"

compress

save "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\ind`x'.dta", replace

clear

}

* Don't loose variables for years when appending (all string)

forval x=2009/2013 {
use "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\ind`x'.dta", clear
tostring P1016_LopNr_PersonNr P1016_LopNr_KU1PeOrgNr FodelseAr Kon LoneInk Alder Sun2000Inr ExamAr AntAns SektorKod KU1Ink KU1SektorKod KU1AstLan KU1AstSNI2007 KU1Ssyk4, replace
save "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\ind`x'.dta", replace
}

forval x=2014/2017 {
use "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\ind`x'.dta", clear
tostring P1016_LopNr_PersonNr P1016_LopNr_KU1PeOrgNr FodelseAr Kon LoneInk Alder Sun2000Inr ExamAr AntAns SektorKod KU1Ink KU1SektorKod KU1AstLan KU1AstSNI2007 KU1Ssyk4 KU1Ssyk4_2012, replace
save "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\ind`x'.dta", replace
}

* Append all years

use "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\ind2009.dta", clear
gen year=2009
forval x=2010/2017 {
append using "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\ind`x'.dta", force
replace year=`x' if year==.
}

save "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\emp09_16.dta", 
replace




********************************************************************
* Take out unique population to use further
********************************************************************

use "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\emp09_16.dta", clear

* Unique individuals
egen tag=tag(P1016_LopNr_PersonNr)
keep if tag==1
keep P1016_LopNr_PersonNr

gen id=P1016_LopNr_PersonNr

save "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\pop.dta", replace


********************************************************************
* Conscription data
********************************************************************

use "\\micro.intra\projekt\P1016$\P1016_Gem\FAME\Raw\military_insark.dta", clear

* Cognitive test scores
keep stp1 stp2 stp3 stp4 id 

* Unique observations
egen tag=tag(id)
keep if tag==1
drop tag

destring stp1 stp2 stp3 stp4, force replace

tostring id, replace

* Merge with unique population and save matches
merge 1:1 id using "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\pop.dta"

keep if _merge==3
drop _merge

save "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\con.dta", replace

********************************************************************
* Experience in ambulance firm (as present in 2009-2017) from 1990.
* Not possible to identify ambulance firms before 2009 so we cannot
* match to earlier ambulance firms.
********************************************************************

use "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\pop.dta", clear

drop id

destring P1016_LopNr_PersonNr, replace

save "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\pop2.dta", replace

* Merge on ambulance population to each year of population wide employer-employee data
* We create dataset pop2.dta that is updated with each year. In this way each 
* individual is observed at the first match only.
 
forval x=1990/2016 {

odbc load, exec("SELECT * FROM Individ_`x';") dsn("P1016")

merge m:1 P1016_LopNr_PersonNr using "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\pop2.dta"

drop if _merge==1

preserve
* Keep if one of the ambulance firms
keep if (P1016_LopNr_KU1PeOrgNr==2183571  | P1016_LopNr_KU1PeOrgNr==1977786  | P1016_LopNr_KU1PeOrgNr==2147066 | P1016_LopNr_KU1PeOrgNr==956005 | P1016_LopNr_KU1PeOrgNr==515029) & _merge==3

* Generate experience variable - first year observed in data

gen exp=`x'
keep P1016_LopNr_PersonNr exp
save "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\exp`x'.dta", replace
restore

drop if (P1016_LopNr_KU1PeOrgNr==2183571  | P1016_LopNr_KU1PeOrgNr==1977786  | P1016_LopNr_KU1PeOrgNr==2147066 | P1016_LopNr_KU1PeOrgNr==956005 | P1016_LopNr_KU1PeOrgNr==515029) & _merge==3
keep P1016_LopNr_PersonNr
save "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\pop2.dta", replace
clear
}

* Merge experience data

use "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\exp1990.dta", clear
forval x=1991/2016 {
append using "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\exp`x'.dta"
}
tostring P1016_LopNr_PersonNr, replace
save "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\exp.dta", replace 


***********************************
* Start data merge
***********************************
* Main dataset
use "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\emp09_16.dta", clear

* Merge conscription data
merge m:1 P1016_LopNr_PersonNr using "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\con.dta" 

drop if _merge==2
drop _merge

* Merge experience
merge m:1 P1016_LopNr_PersonNr using "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\exp.dta" 

drop if _merge==2
drop _merge

* Years of experience
gen exp2=year-exp

destring P1016_LopNr_KU1PeOrgNr, replace

* Workers in ambulance firms (firm id plus sector code (SNI) ambulance firm)
gen amb=0
replace amb=1 if (P1016_LopNr_KU1PeOrgNr==2183571  | P1016_LopNr_KU1PeOrgNr==1977786  | P1016_LopNr_KU1PeOrgNr==2147066 | P1016_LopNr_KU1PeOrgNr==956005 | P1016_LopNr_KU1PeOrgNr==515029) & KU1AstSNI2007=="86902"

* Same firm, set to same
replace P1016_LopNr_KU1PeOrgNr=2183571 if P1016_LopNr_KU1PeOrgNr==956005

* Private firm (private - public firm variable indicated which firm is public)
gen private=1
replace private=0 if P1016_LopNr_KU1PeOrgNr==2147066

* Specialist nurse (education code for specialist nurses)
gen spec=0
replace spec=1 if Sun2000Inr=="723c"

drop id
gen id=P1016_LopNr_PersonNr
destring id, replace

* Remove physicians with higher ecducation 
destring Sun2000niva, replace
drop if Sun2000niva>549

* Pay cannot be over 800k (not for regular employees)
destring LoneInk, replace
replace LoneInk=LoneInk*10
drop if LoneInk>80000 & LoneInk!=.

* Exit sector variable (indicating last year observed in data)
sort id year
egen tr=max(year) if amb==1, by(id)
gen exit=0
replace exit=1 if year==tr
drop tr

* Switching firm
sort id year
gen switch=0
replace switch=1 if amb==1 & P1016_LopNr_KU1PeOrgNr!=P1016_LopNr_KU1PeOrgNr[_n+1] & id==id[_n+1] & exit==0

* Turnover=exit+switch
gen turn=0
replace turn=1 if switch==1 | exit==1

* Age
destring FodelseAr, replace
gen age=year-FodelseAr

* Cognitive scores 1-9, logic, verbal, spatial, technical
foreach x in stp1 stp2 stp3 stp4 {
replace `x'=. if `x'>9
replace `x'=. if `x'==0
}

* Take away 2017 not used in main analysis
drop if year==2017

* Average of the 4 cognitive tests
gen cog=(stp1+stp2+stp3+stp4)/4

save "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\data.dta", replace



****************************************************
* Differences between private/public firms employees
* TABLE VI in the article
****************************************************

* We use variables stp2 and stp3 here to generate a sufficiently large table.
* These values are manually changed later with educational data. See Part 2 of
* this code file.

use "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\data.dta", clear

est clear

* Panel 1: All employees

local k=1
foreach x in turn exp2 age cog stp2 stp3 LoneInk {
qui reg `x' private i.year if amb==1, cluster(id)
est store A`k'
qui sum `x' if amb==1 & private==0
estadd scalar mean=r(mean)
local k=`k'+1
}


lab var private "Private ambulance"

esttab A1 A2 A3 A4 A5 A6 A7  using "//micro.intra/mydocs/mydocs/bjohi2/my documents/OutBox/firm.tex", f plain noobs label collabels(none) nomtitles parentheses booktabs b(3) se(3) keep(private)
esttab A1 A2 A3 A4 A5 A6 A7  using "//micro.intra/mydocs/mydocs/bjohi2/my documents/OutBox/firma.tex", f plain noobs label collabels(none) nomtitles parentheses booktabs b(3) se(3) stats(mean N, fmt(4 0) labels("Public outcome mean" "Observations")) keep("")


* Panel 2: Specialist nurses

est clear

local k=1
foreach x in turn exp2 age cog stp2 stp3 LoneInk {
qui reg `x' private i.year if amb==1 & spec==1, cluster(id)
est store A`k'
qui sum `x' if amb==1  & private==0 & spec==1
estadd scalar mean=r(mean)
local k=`k'+1
}

lab var private "Private ambulance"

esttab A1 A2 A3 A4 A5 A6 A7  using "//micro.intra/mydocs/mydocs/bjohi2/my documents/OutBox/firm2.tex", f plain noobs label collabels(none) nomtitles parentheses booktabs b(3) se(3) keep(private)
esttab A1 A2 A3 A4 A5 A6 A7  using "//micro.intra/mydocs/mydocs/bjohi2/my documents/OutBox/firm2a.tex", f plain noobs label collabels(none) nomtitles parentheses booktabs b(3) se(3) stats(mean N, fmt(4 0) labels("Public outcome mean" "Observations")) keep("")


* Add education by hand to columns 5 and 6, see next section of code.


******************************************************
* Figure, employable specialist nurses
* FIGURE A.4 in the OL Appendix
******************************************************

use "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\data.dta", clear

keep if spec==1
gen one=1
collapse (sum) one, by(amb year)
lab var one "Number of specialist nurses"
lab var year "Year"
tw line one year if amb==1, lpattern(dash) || line one year if amb==0, legend(label(1 "In ambulance sector") label(2 "Outside ambulance sector")) graphregion(color(white))
graph export "\\micro.intra\mydocs\mydocs\bjohi2\my documents\OutBox\nurses.pdf", as(pdf) name("Graph")

*****************************************************
* This part of the code file creates  
* Table B.6 in the OL Appendix.
*****************************************************

* Use main data on all employees in Stockholm that either work for an ambulance firm 
* or that are specialist nurses.

use "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\data.dta", clear

* Take out unique population

keep P1016_LopNr_PersonNr

destring P1016_LopNr_PersonNr, replace

egen tag=tag(P1016_LopNr_PersonNr)

keep if tag==1

drop tag

save "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\ind.dta", replace

* Add information for all years on the population.

clear

forval x=2009/2017 {
odbc load, exec("SELECT * FROM Individ_`x';") dsn("P1016")
keep P1016_LopNr_PersonNr P1016_LopNr_KU1PeOrgNr KU1AstSNI2007 KU1SektorKod KU1AstLan
merge m:1 P1016_LopNr_PersonNr using "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\ind.dta"
keep if _merge==3
gen year=`x'
save "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\i`x'.dta", replace
clear
}

* Append

use "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\i2009.dta", clear
forval x=2010/2017 {
append using "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\i`x'.dta", force
}

*****************************************
* Sectorcode=public=not 21 or 22
* SNI2007: Public Hospital=86102
*****************************************

* Public hospital transfer
gen phosp=0
replace phosp=1 if KU1AstSNI2007=="86102" & (KU1SektorKod!="21" | KU1SektorKod!="22")

keep if phosp==1

rename P1016_LopNr_PersonNr id

append using "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\data.dta"

* Switch job to public hospital

sort id year

gen tph=0
replace tph=1 if phosp[_n+1]==1 & id==id[_n+1] & year+1==year[_n+1]
drop if phosp==1

* Switch2=private to public and vice versa. Change employer AND sector (public/private)

sort id year
gen switch2=switch
replace switch2=0 if private==1 & switch==1 & private==private[_n+1] & id==id[_n+1]

************************************************
* Differences between public and private employees
* in turnover, exit, switch between employers and switch to 
* public hospital.
* TABLE B.1 in OL Appendix
************************************************

est clear

qui reg turn private i.year if amb==1, cluster(id)
est store A1
qui sum turn if private==0
estadd scalar mean=r(mean)

qui reg switch private i.year if amb==1, cluster(id)
est store A2
qui sum switch if private==0
estadd scalar mean=r(mean)

qui reg exit private i.year if  amb==1, cluster(id)
est store A3
qui sum exit if private==0
estadd scalar mean=r(mean)

qui reg switch2 private i.year if amb==1, cluster(id)
est store A4
qui sum switch2 if private==0
estadd scalar mean=r(mean)

qui reg tph private i.year if amb==1, cluster(id)
est store A5
qui sum tph if private==0
estadd scalar mean=r(mean)


lab var private "Private ambulance"

esttab A1 A2 A3 A4 A5 using "//micro.intra/mydocs/mydocs/bjohi2/my documents/OutBox/xfirm2.tex", f plain noobs label collabels(none) nomtitles parentheses booktabs b(3) se(3) keep(private)
esttab A1 A2 A3 A4 A5 using "//micro.intra/mydocs/mydocs/bjohi2/my documents/OutBox/xfirm2a.tex", f plain noobs label collabels(none) nomtitles parentheses booktabs b(3) se(3) stats(mean N, fmt(4 0) labels("Public outcome mean" "Observations")) keep("")


**************************************
* Table wages no employees, B.10 in OL Appendix,
* full time and temporary contracts 2017
**************************************

odbc load, exec("SELECT * FROM Individ_2017;") dsn("P1016")
compress
save "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\all2017.dta", replace


use "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\all2017.dta", clear

keep P1016_LopNr_KU3PeOrgNr P1016_LopNr_KU2PeOrgNr P1016_LopNr_KU1PeOrgNr Sun2000Inr KU1AstLan KU1AstSNI2007 KU2Ink KU1Ink KU2AstLan KU2SektorKod KU3Ink KU3AstLan KU3AstSNI2007 KU3SektorKod LoneInk Sun2000niva


********************************************************************
* Largest source of wage income (we use as full time employment)
********************************************************************

* Save dataset to take out information on primary - secondary - tertiary
* employer. We define primary employer as "full time" employment and other employment
* as temporary employment.

preserve
keep P1016_LopNr_KU1PeOrgNr Sun2000Inr KU1AstLan KU1AstSNI2007 KU1Ink LoneInk Sun2000niva

* Keep if working in Stockholm
keep if KU1AstLan=="01"

* Generate working in ambulance firm
gen amb=0
replace amb=1 if (P1016_LopNr_KU1PeOrgNr==2183571  | P1016_LopNr_KU1PeOrgNr==1977786  | P1016_LopNr_KU1PeOrgNr==2147066 | P1016_LopNr_KU1PeOrgNr==956005 | P1016_LopNr_KU1PeOrgNr==515029) & KU1AstSNI2007=="86902"

* Generate private employer
gen private=1
replace private=0 if P1016_LopNr_KU1PeOrgNr==2147066

* Generate specialist nurse
gen spec=0
replace spec=1 if Sun2000Inr=="723c"

* Remove physicians with higher education
destring Sun2000niva, replace
drop if Sun2000niva>549

* Pay cannot be over 800k (remove managers, owners, etc)
destring LoneInk, replace
replace LoneInk=LoneInk*10
drop if LoneInk>80000 & LoneInk!=.

* Keep ambulance firm employees
keep if amb==1

* Income from primary employer
rename KU1Ink inc

keep spec inc private

gen pt=0

save "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\emp1.dta", replace
restore

********************************************************************
* 2rd largest source of wage income (we use as temporary employment 1)
********************************************************************
* See comments from above as we use the same code but for 
* secondary employment.

preserve
keep P1016_LopNr_KU2PeOrgNr Sun2000Inr KU2AstLan KU2Ink LoneInk Sun2000niva

keep if KU2AstLan=="01"

gen amb=0
replace amb=1 if (P1016_LopNr_KU2PeOrgNr==2183571  | P1016_LopNr_KU2PeOrgNr==1977786  | P1016_LopNr_KU2PeOrgNr==2147066 | P1016_LopNr_KU2PeOrgNr==956005 | P1016_LopNr_KU2PeOrgNr==515029)

gen private=1
replace private=0 if P1016_LopNr_KU2PeOrgNr==2147066

gen spec=0
replace spec=1 if Sun2000Inr=="723c"

* Ta bort läkare och högra utb
destring Sun2000niva, replace
drop if Sun2000niva>549

* Pay cannot be over 800k
destring LoneInk, replace
replace LoneInk=LoneInk*10
drop if LoneInk>80000 & LoneInk!=.

keep if amb==1

rename KU2Ink inc
keep spec inc private

gen pt=1

save "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\emp2.dta", replace
restore

********************************************************************
* 3rd largest source of wage income (temporary employment 2)
********************************************************************

preserve
keep P1016_LopNr_KU3PeOrgNr Sun2000Inr KU3AstLan KU3AstSNI2007 KU3Ink LoneInk Sun2000niva

keep if KU3AstLan=="01"

gen amb=0
replace amb=1 if (P1016_LopNr_KU3PeOrgNr==2183571  | P1016_LopNr_KU3PeOrgNr==1977786  | P1016_LopNr_KU3PeOrgNr==2147066 | P1016_LopNr_KU3PeOrgNr==956005 | P1016_LopNr_KU3PeOrgNr==515029) & KU3AstSNI2007=="86902"

gen private=1
replace private=0 if P1016_LopNr_KU3PeOrgNr==2147066

gen spec=0
replace spec=1 if Sun2000Inr=="723c"

* Ta bort läkare och högra utb
destring Sun2000niva, replace
drop if Sun2000niva>549

* Pay cannot be over 800k
destring LoneInk, replace
replace LoneInk=LoneInk*10
drop if LoneInk>80000 & LoneInk!=.

keep if amb==1

rename KU3Ink inc
keep spec inc private

gen pt=1

save "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\emp3.dta", replace
restore

use "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\emp2.dta", clear
append using "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\emp3.dta"
append using "\\micro.intra\projekt\P1016$\P1016_Gem\Björn Tyrefors\amb\data\emp1.dta"

gen one=1

* To numeric
destring inc, replace

* Wage income in 100 SEK=10 EUR. Rescale to 1 EUR.
replace inc=inc*10

*****************************************
* Start Table
*****************************************

*****************************************
* Wages for temporary and full time employees 
* TABLE B.10 in OL Appendix
*****************************************

* Panel 1. All employees

forval x=0/1 {
local k=1
forval y=0/1 {
qui sum inc if private==`y' & pt==`x'
local n`k'=r(N)
local m`k'=round(r(mean),1)
local k=`k'+1
}
di "& " `n1' " & " `m1' " & " `n2' " & " `m2' " \\" 
}

* Copy paste information to LaTex

 
* Panel 2. Specialist nurses

forval x=0/1 {
local k=1
forval y=0/1 {
qui sum inc if private==`y' & pt==`x' & spec==1
local n`k'=r(N)
local m`k'=round(r(mean),1)
local k=`k'+1
}
di "& " `n1' " & " `m1' " & " `n2' " & " `m2' " \\" 
}


* Copy paste :)



******************************************************
* PART 2.
*******************
* This code produce additional output for TABLE VI in published article
* on educational attainment in the 9th grade.

* We use residents in Region Stockholm that work in an
* ambulance firm. We restrict the sample on income and
* educational attainment. We use all employees and
* specialist nurses according to professional codes.

* The following data sources from Statistics Sweden was
* used to produce the output:
* LISA 2009-2017
* RTB 2009-2017
* IoT 2009-2016
* Åk9 betyg (grade 9 information) 1988-2010
* UREG (utbildningsregistret) (education) 2009-2016

****************************************************
* Start code:
****************************************************

* Professional code, ssyk4, from register LISA (2009-2017)

forval x=2009/2013 {

use "/home/rawdata/LISA/LISA_INDIVID_`x'.dta", clear

* Save information on professional code, firm sector, and identifyers for
* three largest employers in terms of wage income.

keep lopnr ssyk4 ku1astsni2007 ku1peorgnrlopnr ku2peorgnrlopnr ku3peorgnrlopnr

* Unique observations
egen tag=tag(lopnr)
keep if tag==1
drop tag

* Merge with full population (residence information)
merge 1:m lopnr using "/home/rawdata/population/RTB_`x'.dta"
keep if _merge==3
drop _merge

egen tag=tag(lopnr)
keep if tag==1
drop tag

* Keep if Stockholm resident
keep if lan_res==1

gen year=`x'

compress

save "/home/knudan/lisa`x'.dta", replace

}

************************************************************
* Same as above for year 2014
************************************************************

use "/home/rawdata/LISA/LISA_INDIVID_2014.dta", clear

* Save information on professional code, firm sector, and identifyers for
* three largest employers in terms of wage income.

keep lopnr ssyk4_2012 ku1astsni2007 ku1peorgnrlopnr ku2peorgnrlopnr ku3peorgnrlopnr

egen tag=tag(lopnr)
keep if tag==1
drop tag

merge 1:m lopnr using "/home/rawdata/population/RTB_2014.dta"
keep if _merge==3
drop _merge

egen tag=tag(lopnr)
keep if tag==1
drop tag

keep if lan_res==1

gen year=2014

compress

save "/home/knudan/lisa2014.dta", replace


************************************************************
* Same as above for year 2015-2017
************************************************************

forval x=2015/2017 {

use "/home/rawdata/LISA/LISA_INDIVID_`x'.dta", clear

keep lopnr ssyk4_2012 ku1astsni2007 ku1peorgnrlopnr ku2peorgnrlopnr ku3peorgnrlopnr

* Unique observations
egen tag=tag(lopnr)
keep if tag==1
drop tag

* Merge with full population for given year
merge 1:m lopnr using "/home/rawdata/population/RTB`x'.dta"
keep if _merge==3
drop _merge

* Unique observations
egen tag=tag(lopnr)
keep if tag==1
drop tag

* County of residence (Region Stockholm)
gen lan_res=substr(kommun,1,2)

destring lan_res, replace

keep if lan_res==1

gen year=`x'

compress

save "/home/knudan/lisa`x'.dta", replace

}


*******************************************************
* Take out full unique population
*******************************************************

use "/home/knudan/lisa2009.dta", clear

forval x=2010/2017 {
append using "/home/knudan/lisa`x'.dta"
}

egen tag=tag(lopnr)

keep if tag==1

keep lopnr

save "/home/knudan/pop.dta", replace

*******************************************
* Add sun2000 nivå code for sample restriction
* not to include individuals with more than 5 years of education
*******************************************

forval x=2009/2015 {
use "/home/rawdata/education/Lev_Utb_`x'.dta", clear

* Unique observations
duplicates t lopnr, gen(dup)
drop if dup>0
drop dup

gen year=`x'

* Merge with our population
merge 1:1 lopnr using "/home/knudan/pop.dta"
keep if _merge==3
drop _merge

compress

save "/home/knudan/sun`x'.dta", replace
}

**********************************************
* Same as above: Other registry name for 2016-2017
**********************************************
forval x=2016/2017 {

use "/home/rawdata/education/UREG`x'.dta", clear

* Unique observations
duplicates t lopnr, gen(dup)
drop if dup>0
drop dup

* Merge with our population
merge 1:1 lopnr using "/home/knudan/pop.dta"
keep if _merge==3
drop _merge

gen year=`x'

compress

save "/home/knudan/sun`x'.dta", replace

}

********************************************
* Take out ambulance employees population
********************************************
* Append all years of residents in Region Stockholm

use "/home/knudan/lisa2009.dta", clear
forval x=2010/2017 {
append using "/home/knudan/lisa`x'.dta"
}

* Keep if ambulance firms (from employment structure, nurses etc.)
keep if ku1peorgnrlopnr==45996283 | ku1peorgnrlopnr==45077114 | ku1peorgnrlopnr==46285606 | ku1peorgnrlopnr==45128263


egen tag=tag(lopnr)
keep if tag==1
keep lopnr
save  "/home/knudan/apop.dta", replace

*******************************************
* Add income (IoT) for sample constraint
* not to keep incomes above 800k SEK per year
* which indicates managers and owners
*******************************************

* Keep only wage income variable and identifier
* Take out unique observations
* Merge with ambulance population
* Save

forval x=2009/2014 {
use "/home/rawdata/IoT/Lev_IoT_`x'.dta", clear
keep tlont lopnr
egen tag=tag(lopnr)
keep if tag==1
drop tag
merge 1:1 lopnr using "/home/knudan/apop.dta"
keep if _merge==3
drop _merge
save "/home/knudan/iot`x'.dta", replace
}

forval x=2015/2017 {
use "/home/rawdata/IoT/IOT_`x'.dta", clear
keep tlont lopnr
egen tag=tag(lopnr)
keep if tag==1
drop tag
merge 1:1 lopnr using "/home/knudan/apop.dta"
keep if _merge==3
drop _merge
save "/home/knudan/iot`x'.dta", replace
}

* Merge all
use "/home/knudan/iot2009.dta", clear
gen year=2009
forval x=2010/2017 {
append using "/home/knudan/iot`x'.dta"
replace year=`x' if year==.
}

save "/home/knudan/iot.dta"



***************************************************
* Grade 9 grades GPA and math 1988-2010 (ages 24-46)
***************************************************

forval x=1988/1998 {
use "/home/rawdata/education/Lev_Ak9_`x'.dta", clear

* Unique observations
egen tag=tag(lopnr)
keep if tag==1
drop tag

* Merge with population
merge 1:1 lopnr using "/home/knudan/pop.dta"
keep if _merge==3
drop _merge
compress
save "/home/knudan/ak9`x'.dta", replace
}

forval x=1999/2010 {
use "/home/rawdata/education/Lev_Ak9_`x'.dta", clear
egen tag=tag(lopnr)
keep if tag==1
drop tag
merge 1:1 lopnr using "/home/knudan/pop.dta"
keep if _merge==3
drop _merge
compress
save "/home/knudan/ak9`x'.dta", replace
}

* Changes in GPA:
* Medelbetyg -1997
* Meritvärde 1998-2010

* Recode GPA as mean zero and std one in the population
forval x=1988/1997 {
use "/home/knudan/ak9`x'.dta", clear
qui sum medelbetyg
gen gpa=(medelbetyg-r(mean))/r(sd)
gen mat=mas
replace mat=maa if mat=="0"
destring mat, replace force
replace mat=1 if mat==1 | mat==2
replace mat=2 if mat==3
replace mat=3 if mat==4
replace mat=4 if mat==5
qui sum mat
gen math=(mat-r(mean))/r(sd)
keep lopnr math gpa
compress
save "/home/knudan/a9`x'.dta", replace
}

forval x=1998/2010 {
use "/home/knudan/ak9`x'.dta", clear
qui sum meritvarde
gen gpa=(meritvarde-r(mean))/r(sd)
drop if ma=="A" | ma=="F"
gen mat=0
replace mat=1 if ma=="1"
replace mat=2 if ma=="G"
replace mat=3 if ma=="V"
replace mat=4 if ma=="M"
drop if mat==0
qui sum mat
gen math=(mat-r(mean))/r(sd)
keep lopnr math gpa
compress
save "/home/knudan/a9`x'.dta", replace
}

* Append all
use "/home/knudan/a91988.dta"
forval x=1989/2010 {
append using "/home/knudan/a9`x'.dta"
}

* Unique observations
egen tag=tag(lopnr)
keep if tag==1
drop tag

save "/home/knudan/ak9.dta", replace


******************************************
* Set up dataset
******************************************

use "/home/knudan/lisa2009.dta", clear

forval x=2010/2017 {
append using "/home/knudan/lisa`x'.dta"
}

* Ssyk 2012: 2223, 2226, 2228 = relevant specialist codes
* SSYK <2012: 2233 (including ED nurses)

* Specialist nurses using professional codes
gen snurse=0
replace snurse=1 if ssyk4=="2233"
replace snurse=1 if ssyk4_2012=="2223" | ssyk4_2012=="2226" | ssyk4_2012=="2228"

* Add sun2000 nivå, educational attainment for each year

gen s2000=""
gen exar=""
forval x=2009/2017 {
merge 1:1 lopnr year using "/home/knudan/sun`x'.dta"
drop if _merge==2
drop _merge
replace s2000=sun2000niva if year==`x'
replace exar=examar if year==`x'
drop sun2000niva examar
}

destring s2000, replace

destring exar, replace force


* Account for differences in nurse training length over time
* using these professional codes:
* 1993 nurse training becomes 3 years in length

gen spec=0
replace spec=1 if s2000>529 & exar<1993
replace spec=1 if s2000>539 & exar>=1993

save "/home/knudan/lisa.dta", replace

*********************************************
* Merge all
*********************************************

use  "/home/knudan/lisa.dta",  clear

* Variable defining ambulance firms
gen amb=0
replace amb=1 if (ku1peorgnrlopnr==45996283 | ku1peorgnrlopnr==45077114 | ku1peorgnrlopnr==46285606 |ku1peorgnrlopnr==45128263) & ku1astsni2007=="86902"

* Here, we do not have county of workplace, only
* county of residence which can result in somewhat
* fewer observations.

* Merge educational data
merge m:1 lopnr using "/home/knudan/ak9.dta"
drop if _merge==2
drop _merge

* Merge income data
merge m:1 lopnr year using "/home/knudan/iot.dta"
drop if _merge==2
drop _merge

* Dummy for employed in private firm
gen private=1
replace private=0 if ku1peorgnrlopnr==46285606

compress

save "/home/knudan/data.dta", replace


* We need only 2 estimates from this data, educational outcomes 
* in 9th grade

use "/home/knudan/data.dta", clear

* Use only ambulance employees
keep if amb==1

* Use only with wage income less than 800k SEK
drop if tlont>800000

* More education than required (physicians etc.) 30 obs del
drop if s2000>549

drop if year==2017

reg gpa private i.year if amb==1, cluster(lopnr)
reg math private i.year if amb==1, cluster(lopnr)

reg gpa private i.year if amb==1 & spec==1, cluster(lopnr)
reg math private i.year if amb==1 & spec==1, cluster(lopnr)

* Move by hand to TABLE VI in article.

clear 

exit

****************************************
* END CODE
****************************************